package com.example.dao;

import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.example.domain.CourseTeacher;

@Repository
public interface CourseTeacherDao extends JpaRepository<CourseTeacher, Integer> {

    @Transactional
    @Modifying
    Integer deleteByCourseTeacherId(Integer courseTeacherId);

    List<CourseTeacher> findByCourseTeacherId(Integer courseTeacherId);

    @Transactional
    @Modifying
    Integer deleteByCourseTeacherIdIn(List<Integer> listId);

    @Query(nativeQuery = true, value = "SELECT u.user_Id as userId,u.account as userName,u.nickName \r\n" +
            "from (users u left join user_role ur on u.user_Id = ur.user_Id )\r\n" +
            "left join role r on r.role_Id = ur.role_Id\r\n" +
            "where r.roleName = '老师' ")
    List<Map<String, Object>> queryAllTeachers();

    @Query(nativeQuery = true, value = "select c.course_Id as courseId,c.courseName,ct.typeName \r\n" +
            "from course c left join course_type ct on c.course_type_Id = ct.course_type_Id")
    List<Map<String, Object>> queryAllCourseName();

    @Query(nativeQuery = true, value = "SELECT u.account as userName,u.nickName,co.courseName,ct.* \r\n" +
            "FROM (users u INNER JOIN course_teacher ct ON ct.user_Id = u.user_Id)\r\n" +
            "INNER JOIN course co ON ct.course_Id = co.course_Id")
    List<Map<String, Object>> queryAllTeacherAndCourseInfo();

    @Query(nativeQuery = true, value = "SELECT u.account as userName,u.nickName,co.courseName,ct.* \r\n" +
            "FROM (users u INNER JOIN course_teacher ct ON ct.user_Id = u.user_Id)\r\n" +
            "INNER JOIN course co ON ct.course_Id = co.course_Id\r\n" +
            "WHERE u.account like CONCAT('%',:userName,'%')")
    List<Map<String, Object>> findByUserNameLike(@Param("userName") String userName);

    @Query(nativeQuery = true, value = "SELECT u.account as userName,u.nickName,co.courseName,ct.* \r\n" +
            "FROM (users u INNER JOIN course_teacher ct ON ct.user_Id = u.user_Id)\r\n" +
            "INNER JOIN course co ON ct.course_Id = co.course_Id\r\n" +
            "WHERE u.nickName like CONCAT('%',:nickName,'%')")
    List<Map<String, Object>> findByNickNameLike(@Param("nickName") String nickName);

    @Query(nativeQuery = true, value = "SELECT u.account as userName,u.nickName,co.courseName,ct.* \r\n" +
            "FROM (users u INNER JOIN course_teacher ct ON ct.user_Id = u.user_Id)\r\n" +
            "INNER JOIN course co ON ct.course_Id = co.course_Id\r\n" +
            "WHERE co.courseName like CONCAT('%',:courseName,'%')")
    List<Map<String, Object>> findByCourseNameLike(@Param("courseName") String courseName);

}
